﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class SocialWorkEntity
    {
        public Int32 Id { get; set; }
        public Int32 MemId { get; set; }
        public string Place { get; set; }
        public string JobTittle { get; set; }
        public string Supervisor { get; set; }
    }

    public class SocialWork
    {
        private static string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static string CmdText = "";

        private static SocialWorkEntity FillEntityFromReader(OleDbDataReader reader)
        {
            SocialWorkEntity x = new SocialWorkEntity();
            x.Id = Int32.Parse(reader["Id"].ToString());
            x.MemId = Int32.Parse(reader["MemId"].ToString());
            if (reader["Place"] != null)
                x.Place = reader["Place"].ToString();
            if (reader["JobTittle"] != null)
                x.JobTittle = reader["JobTittle"].ToString();
            if (reader["Supervisor"] != null)
                x.Supervisor = reader["Supervisor"].ToString();
            return x;
        }


        public static List<SocialWorkEntity> GetAllSocialWorks()
        {
            SocialWorkEntity x;
            List<SocialWorkEntity> SocialWorkList = new List<SocialWorkEntity>();
            CmdText = "Select * from SocialWork ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    SocialWorkList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return SocialWorkList;

        }


        public static SocialWorkEntity GetSocialWorkByName(Int32 Num)
        {
            SocialWorkEntity x = null ;
            CmdText = "Select * from SocialWork where MemId = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@MemId", Num);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);
               
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }


        public static List<SocialWorkEntity> GetSocialWorkByMemId(Int32 MemId)
        {
            SocialWorkEntity x;
            List<SocialWorkEntity> SocialWorkList = new List<SocialWorkEntity>();
         
            CmdText = "Select * from SocialWork where MemId = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@MemId", MemId);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    SocialWorkList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return SocialWorkList;
        }



        public static bool Add(SocialWorkEntity P)
        {
            bool flag = true;

            CmdText = "INSERT INTO SocialWork (Place,JobTittle,Supervisor,MemId) values (?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Place", P.Place);
            cmd.Parameters.AddWithValue("@JobTittle", P.JobTittle);
            cmd.Parameters.AddWithValue("@Supervisor", P.Supervisor);
            cmd.Parameters.AddWithValue("@MemId", P.MemId);
            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }


        public static bool Update(SocialWorkEntity P)
        {
            bool flag = true;

            CmdText = "Update SocialWork set Place = ? , JobTittle = ? , Supervisor = ? , MemId = ? where Id=? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Place", P.Place);
            cmd.Parameters.AddWithValue("@JobTittle", P.JobTittle);
            cmd.Parameters.AddWithValue("@Supervisor", P.Supervisor);
            cmd.Parameters.AddWithValue("@MemId", P.MemId);
            cmd.Parameters.AddWithValue("@Id", P.Id);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
              flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }


        public static bool Delete(int Id)
        {
             bool flag = true;
            CmdText = "delete from SocialWork where Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

    }
}
